﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text;
using CRMFramework;

namespace CRMBiz.Contact
{
    public static class BusinessHandler
    {
        /// <summary>
        /// function to get Business info list
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns>BusinessData</returns>
        public static DataSet SelectBusiness(int pageIndex, int pageSize, int CurrentContact_id)
        {
            DataSet ds;
            try
            {
                var sbSql = new StringBuilder("SELECT *,DATE_FORMAT(RegistrationDate,'%d-%b-%Y') as RegDate,(select count(*) from crm_business where CurrentContact_id=" + CurrentContact_id + ") as Total from crm_business where CurrentContact_id=" + CurrentContact_id + " order by Entity  desc ");
                sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                ds = DBhelper.ExecuteDataset(sbSql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        /// <summary>
        /// function to update Business info
        /// </summary>
        ///<param name="BusinessID"></param>
        /// <returns>BusinessData</returns>
        public static int EditBusinessInfo(int BusinessID, int CurrentContact_id, string Stage, string Entity, string Relationship1, string Relationship2, string ABN, string Nature, string Address, string Website, string MoneyDetail, string Income, string Repay, string RepayBy, string Trustee, string Beneficiary, string CashSale, string EftposSale, string UsbYear, string UsbMonth, string CompanyName, string CompanySecurity, string SecurityStatus, string RegistrationDate, string PriorChargee, string SubsequentChargee, string CompanyComment)
        {
            int intReturnValue = 0;
            try
            {
                DateTime dtDateBirth = new DateTime();
                DateTimeFormatInfo dtfi = new DateTimeFormatInfo();
                dtfi.ShortDatePattern = "yyyy-MM-dd";
                dtfi.DateSeparator = "-";
                if (!string.IsNullOrEmpty(RegistrationDate))
                    dtDateBirth = Convert.ToDateTime(RegistrationDate, dtfi);

                var para = new[] {
                     DBhelper.SetParameter("@BusinessID", BusinessID),
                    DBhelper.SetParameter("@CurrentContact_id", CurrentContact_id),
                    DBhelper.SetParameter("@Stage", Stage),
                    DBhelper.SetParameter("@Entity", Entity),
                    DBhelper.SetParameter("@Relationship1", Relationship1),
                    DBhelper.SetParameter("@Relationship2", Relationship2),
                    DBhelper.SetParameter("@ABN", ABN),
                    DBhelper.SetParameter("@Nature", Nature),
                    DBhelper.SetParameter("@Address",Address),
                    DBhelper.SetParameter("@Website",Website),
                    DBhelper.SetParameter("@MoneyDetail",MoneyDetail),
                    DBhelper.SetParameter("@Income",Income),
                    DBhelper.SetParameter("@Repay",Repay),
                    DBhelper.SetParameter("@RepayBy",RepayBy),
                    DBhelper.SetParameter("@Trustee",Trustee),
                    DBhelper.SetParameter("@Beneficiary",Beneficiary),   

                    DBhelper.SetParameter("@CashSale",CashSale),
                    DBhelper.SetParameter("@EftposSale",EftposSale),   
                    DBhelper.SetParameter("@UsbYear",UsbYear),
                    DBhelper.SetParameter("@UsbMonth",UsbMonth),   
                    DBhelper.SetParameter("@CompanyName",CompanyName),
                    DBhelper.SetParameter("@CompanySecurity",CompanySecurity),   
                    DBhelper.SetParameter("@SecurityStatus",SecurityStatus),
                    DBhelper.SetParameter("@RegistrationDate",dtDateBirth),   
                    DBhelper.SetParameter("@PriorChargee",PriorChargee),
                    DBhelper.SetParameter("@SubsequentChargee",SubsequentChargee),
                    DBhelper.SetParameter("@CompanyComment",CompanyComment)
                };

                string sbSql = "update crm_business set Stage =@Stage,Entity=@Entity,Relationship1=@Relationship1,Relationship2=@Relationship2,ABN=@ABN,Nature=@Nature,Address=@Address,Website=@Website,MoneyDetail=@MoneyDetail,Income=@Income,Repay=@Repay,RepayBy=@RepayBy,Trustee=@Trustee,Beneficiary=@Beneficiary,CashSale=@CashSale,EftposSale=@EftposSale,UsbYear=@UsbYear,UsbMonth=@UsbMonth,CompanyName=@CompanyName,CompanySecurity=@CompanySecurity,SecurityStatus=@SecurityStatus,RegistrationDate=@RegistrationDate,PriorChargee=@PriorChargee,SubsequentChargee=@SubsequentChargee,CompanyComment=@CompanyComment where BusinessID = @BusinessID";
                DBhelper.ExecuteNonQuery(sbSql, para);
                intReturnValue = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        /// <summary>
        /// function to add new business info
        /// </summary>
        /// <param name="Contact_ID"></param>
        /// <returns>dataset</returns>
        public static int AddBusinessInfo(string Stage, string Entity, string Relationship1, string Relationship2, string ABN, string Nature, string Address, string Website, string MoneyDetail, string Income, string Repay, string RepayBy, string Trustee, string Beneficiary, string CashSale, string EftposSale, string UsbYear, string UsbMonth, string CompanyName, string CompanySecurity, string SecurityStatus, string RegistrationDate, string PriorChargee, string SubsequentChargee, string CompanyComment, int CurrentContact_id)
        {
            int intReturnValue = 0;
            try
            {
                DateTime dtDateBirth = new DateTime();
                DateTimeFormatInfo dtfi = new DateTimeFormatInfo();
                dtfi.ShortDatePattern = "yyyy-MM-dd";
                dtfi.DateSeparator = "-";
                if (!string.IsNullOrEmpty(RegistrationDate))
                    dtDateBirth = Convert.ToDateTime(RegistrationDate, dtfi);

                var para = new[] {
                    DBhelper.SetParameter("@Stage", Stage),
                    DBhelper.SetParameter("@Entity", Entity),
                    DBhelper.SetParameter("@Relationship1", Relationship1),
                    DBhelper.SetParameter("@Relationship2", Relationship2),
                    DBhelper.SetParameter("@ABN", ABN),
                    DBhelper.SetParameter("@Nature", Nature),
                    DBhelper.SetParameter("@Address",Address),
                    DBhelper.SetParameter("@Website",Website),
                    DBhelper.SetParameter("@MoneyDetail",MoneyDetail),
                    DBhelper.SetParameter("@Income",Income),
                    DBhelper.SetParameter("@Repay",Repay),
                    DBhelper.SetParameter("@RepayBy",RepayBy),
                    DBhelper.SetParameter("@Trustee",Trustee),
                    DBhelper.SetParameter("@Beneficiary",Beneficiary),   
                    DBhelper.SetParameter("@CashSale",CashSale),
                    DBhelper.SetParameter("@EftposSale",EftposSale),   
                    DBhelper.SetParameter("@UsbYear",UsbYear),
                    DBhelper.SetParameter("@UsbMonth",UsbMonth),   
                    DBhelper.SetParameter("@CompanyName",CompanyName),
                    DBhelper.SetParameter("@CompanySecurity",CompanySecurity),   
                    DBhelper.SetParameter("@SecurityStatus",SecurityStatus),
                    DBhelper.SetParameter("@RegistrationDate",dtDateBirth),   
                    DBhelper.SetParameter("@PriorChargee",PriorChargee),
                    DBhelper.SetParameter("@SubsequentChargee",SubsequentChargee),
                    DBhelper.SetParameter("@CompanyComment",CompanyComment),
                    DBhelper.SetParameter("@CurrentContact_id",CurrentContact_id)
                };

                string sbSql = "insert into crm_business(Stage,Entity,Relationship1,Relationship2,ABN,Nature,Address,Website,MoneyDetail,Income,Repay,RepayBy,Trustee,Beneficiary,CashSale,EftposSale,UsbYear,UsbMonth,CompanyName,CompanySecurity,SecurityStatus,RegistrationDate,PriorChargee,SubsequentChargee,CompanyComment,CurrentContact_id) values(@Stage,@Entity,@Relationship1,@Relationship2,@ABN,@Nature,@Address,@Website,@MoneyDetail,@Income,@Repay,@RepayBy,@Trustee,@Beneficiary,@CashSale,@EftposSale,@UsbYear,@UsbMonth,@CompanyName,@CompanySecurity,@SecurityStatus,@RegistrationDate,@PriorChargee,@SubsequentChargee,@CompanyComment,@CurrentContact_id)";
                DBhelper.ExecuteNonQuery(sbSql, para);
                intReturnValue = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        /// <summary>
        /// function to delete new business info
        /// </summary>
        /// <param name="BusinessID"></param>
        /// <returns>dataset</returns>
        public static bool DeleteBusinessInfo(int BusinessID)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@BusinessID", BusinessID),
                };

                DBhelper.ExecuteNonQuery("delete from crm_business where BusinessID=@BusinessID", para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }
    }
}
